IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_MediaSelectAll]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_MediaSelectAll]
GO
create procedure [dbo].[cms_sp_MediaSelectAll]
(
@SiteGroupId int,
@SiteId int,
@MimeType varchar(200) = null
)
as
if @MimeType is null or @MimeType not in ('video', 'audio', 'application', 'image')
   set @MimeType = '%'
select *
  from (
	select 
		Media.MediaId,
		Media.SiteId,
		Media.SiteGroupId,
		Media.FolderName,
		Media.Description,
		(select count(MediaContents.MediaContentId) from dbo.MediaContents 
			inner join dbo.MediaContentFileLinks 
			   on MediaContents.MediaContentId = MediaContentFileLinks.MediaContentId and IsPrimary=1
			inner join dbo.MediaFiles
			   on MediaFiles.MediaFileId = MediaContentFileLinks.MediaFileId 
			inner join dbo.MimeTypes
			   on MediaFiles.MimeTypeId = MimeTypes.MimeTypeId
			where MediaContents.MediaId = Media.MediaId and 
			   ((MediaContents.SiteGroupId is null and MediaContents.SiteId is null) or 
     			(MediaContents.SiteGroupId = @SiteGroupId and MediaContents.SiteId is null) or
	    		(MediaContents.SiteGroupId = @SiteGroupId and MediaContents.SiteId = @SiteId)) and
	    		MimeTypes.ContentType like @MimeType+'%') as TotalMediaContent,
		Media.CreatedBy,
		Media.DateCreated,
		Media.LastUpdatedBy,
		Media.DateLastUpdated,
		Media.ParentMediaId
	from dbo.Media
	where ((Media.SiteGroupId is null and Media.SiteId is null) or 
		(Media.SiteGroupId = @SiteGroupId and Media.SiteId is null) or
		(Media.SiteGroupId = @SiteGroupId and Media.SiteId = @SiteId)) and ParentMediaId is null
	) Results
	where @MimeType = '%' or TotalMediaContent > 0 


GO

